package com.ruoyi.system.protocol.impl;


import com.ruoyi.common.config.DbConfig;
import com.ruoyi.common.constant.ClassesConstants;
import com.ruoyi.common.utils.PropertiesUtil;
import com.ruoyi.system.protocol.IDynamicTableService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class DynamicTableServiceImpl implements IDynamicTableService {

    private final static Logger LOGGER = LoggerFactory.getLogger(DynamicTableServiceImpl.class);


    @Override
    public void createTable(String tableName, String tableComment) {
        String sql = "CREATE TABLE IF NOT EXISTS  `"+ ClassesConstants.RESOURCE_TABLE_PREFIX +tableName+"` (" +
                "  `id` varchar(50) NOT NULL COMMENT '唯一id'," +
                "  `create_time` varchar(20) DEFAULT '' COMMENT '创建时间'," +
                "  `update_time` varchar(20) DEFAULT '' COMMENT '修改时间'," +
                "  `create_user_name` varchar(50) DEFAULT '' COMMENT '创建人昵称'," +
                "  `create_user_id` varchar(50) DEFAULT '' COMMENT '创建人ID'," +
                "  `update_user_name` varchar(50) DEFAULT '' COMMENT '修改人昵称'," +
                "  `update_user_id` varchar(50) DEFAULT '' COMMENT '修改人ID'," +
                "  `create_dept_name` varchar(50) DEFAULT '' COMMENT '创建人部门名称'," +
                "  `create_dept_id` varchar(50) DEFAULT '' COMMENT '创建人部门名称'," +
                "  `status` varchar(50) DEFAULT '0' COMMENT '状态：0待审核；1已通过；2未通过；'," +
                "  `refuse_explain` varchar(255) DEFAULT '' COMMENT '拒绝说明'," +
                "  PRIMARY KEY (`id`) USING BTREE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='"+tableComment+"'";

        LOGGER.info("知识库创建表SQL:{}", sql);
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            //获取执行sql的对象
            pst = conn.prepareStatement(sql);
            //执行sql
            pst.executeUpdate();
        }catch (Exception e){
            LOGGER.error("创表失败:"+ e.getMessage());
        }finally {
            release(rs, pst, conn);
        }
    }

    @Override
    public void createOpTable(String tableName, String tableComment) {
        String sql = "CREATE TABLE IF NOT EXISTS  `"+ ClassesConstants.RESOURCE_TABLE_PREFIX +tableName+"` (" +
                "  `id` varchar(50) NOT NULL COMMENT '唯一id'," +
                "  `source_data_id` varchar(50) NOT NULL COMMENT '源数据ID'," +
                "  `target_data_id` varchar(50) NOT NULL COMMENT '关联数据ID'," +
                "  `source_class_en` varchar(50) NOT NULL COMMENT '源数据类'," +
                "  `target_class_en` varchar(50) NOT NULL COMMENT '关联数据类'," +
                "  `create_time` varchar(20) DEFAULT '' COMMENT '创建时间'," +
                "  PRIMARY KEY (`id`) USING BTREE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='"+tableComment+"'";

        LOGGER.info("知识库创建关联表SQL:{}", sql);
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            //获取执行sql的对象
            pst = conn.prepareStatement(sql);
            //执行sql
            pst.executeUpdate();
        }catch (Exception e){
            LOGGER.error("创表失败:"+ e.getMessage());
        }finally {
            release(rs, pst, conn);
        }
    }


    @Override
    public void deleteTable(String tableName) {
        String sql = "DROP TABLE `"+ ClassesConstants.RESOURCE_TABLE_PREFIX +tableName+"`";
        LOGGER.info("知识库删除表SQL:{}", sql);
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            //获取执行sql的对象
            pst = conn.prepareStatement(sql);
            //执行sql
            pst.executeUpdate();
        }catch (Exception e){
            LOGGER.error("删表失败:"+ e.getMessage());
        }finally {
            release(rs, pst, conn);
        }
    }

    @Override
    public void createColumn(String tableName, String columnName, String columnComment, String columnLength, String columnType) {
        String sql="ALTER TABLE `"+ ClassesConstants.RESOURCE_TABLE_PREFIX +tableName+"` ADD "+ columnName +" "+columnType+"("+columnLength+") DEFAULT '' COMMENT '"+columnComment+"'";
        if("longtext".equals(columnType)){
            sql="ALTER TABLE `hymax_"+tableName+"` ADD `"+ columnName +"` "+columnType+" COMMENT '"+columnComment+"'";
        }
        LOGGER.info("知识库添加字段SQL:{}", sql);
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            //获取执行sql的对象
            pst = conn.prepareStatement(sql);
            //执行sql
            pst.executeUpdate();
        }catch (Exception e){
            LOGGER.error("添加字段失败:"+ e.getMessage());
        }finally {
            release(rs, pst, conn);
        }
    }

    @Override
    public void deleteColumn(String tableName, String columnName) {
        String sql = "alter table `"+ ClassesConstants.RESOURCE_TABLE_PREFIX +tableName+"` drop column `"+columnName+"`";
        LOGGER.info("知识库删除字段SQL:{}", sql);
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            //获取执行sql的对象
            pst = conn.prepareStatement(sql);
            //执行sql
            pst.executeUpdate();
        }catch (Exception e){
            LOGGER.error("删除字段失败:"+ e.getMessage());
        }finally {
            release(rs, pst, conn);
        }
    }

    /**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    @Override
    public List<String> getColumns(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = null;
        PreparedStatement pStemt = null;
        ResultSetMetaData rsmd = null;
        String tableSql = "SELECT * FROM " + tableName;
        try {
            conn = DriverManager.getConnection(
                    DbConfig.JDBC_URL,
                    DbConfig.JDBC_USERNAME,
                    DbConfig.JDBC_PASSWORD
            );
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnNames failure", e);
        } finally {
            release(null, pStemt, conn);
        }
        return columnNames;
    }

    //释放资源
    public static void release(ResultSet rs, PreparedStatement pst, Connection conn){
        try{
            if(rs!=null){
                rs.close();
            }
            if(pst!=null){
                pst.close();
            }
            if(conn!=null){
                conn.close();
            }
        }catch (Exception e){
            LOGGER.error("close ResultSet failure", e);
        }
    }
}
